IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageControlsSelectByPageId]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageControlsSelectByPageId]
GO
create procedure [dbo].[cms_sp_PageControlsSelectByPageId]
@PageId int,
@IsInAdministration bit = null,
@PageControlTypeParameter nvarchar(150)
as
if @IsInAdministration is null
begin
	set @IsInAdministration = 0
end
declare @IsPublished bit
set @IsPublished = 1
select 
	PageControls.PageControlId,
	PageControls.PageId,
	PageControls.TemplateRegionId,
	PageControls.ModuleId,
	PageControls.PageControlTypeKey,
	PageControls.PageControlViewValue,
	PageControls.PageControlModelValue,
	PageControls.PageControlContentId,
	PageControls.PageControlOrder,
	(select			
		Contents.ContentBody 
	from dbo.Contents
	where Contents.ContentId = PageControls.PageControlContentId
	) as PageControlContent
from dbo.PageControls
inner join dbo.EntityRevisions
	on EntityRevisions.EntityId = PageControls.PageControlId and 
	EntityRevisions.EntityTypeParameter = @PageControlTypeParameter
where PageControls.PageId = @PageId and
	((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
	(@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1)) and
	((@IsInAdministration = 1) or
	 (@IsInAdministration = 0 and EntityRevisions.IsPublished = @IsPublished)) and
	EntityRevisions.IsDeleted = 0 
order by PageControls.PageControlOrder


GO

